package com.es.dao.impl;

import com.es.JDBCUtils;
import com.es.dao.StudentDaoI;
import com.es.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDaoI {
    @Override
    public int save(Student student) {

        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = JDBCUtils.getConnection("mysql");
            String sql = "insert into student(id,name,birthday,address,phone) values (null, ?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setDate(2, new Date(student.getBirthday().getTime()));
            ps.setString(3, student.getAddress());
            ps.setString(4, student.getPhone());
            //执行 交互
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }
        return 0;
    }

    @Override
    public int update(Student student) {
        Connection connection = null;
        PreparedStatement ps = null;
        String sql = "update student set name=?,birthday=?,address=?,phone=? where id = ?";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setDate(2, new Date(student.getBirthday().getTime()));
            ps.setString(3, student.getAddress());
            ps.setString(4, student.getPhone());
            ps.setInt(5, student.getId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }
        return 0;
    }

    @Override
    public int delete(Integer id) {
        Connection connection = null;
        PreparedStatement ps = null;
        String sql = "delete from student where id = ?";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }

        return 0;
    }

    @Override
    public List<Student> list() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<Student> list = new ArrayList<>();

        String sql = "select id,name,birthday,address,phone from student";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();

            while (resultSet.next()) {
                Student stu = new Student();
                stu.setId(resultSet.getInt("id"));
                stu.setAddress(resultSet.getString("address"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setName(resultSet.getString("name"));
                stu.setBirthday(new java.util.Date(resultSet.getDate("birthday").getTime()));
                list.add(stu);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }
        return null;
    }

    @Override
    public Student get(Integer id) {

        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        String sql = "select id,name,birthday,address,phone from student where id =?";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                Student stu = new Student();
                stu.setId(resultSet.getInt("id"));
                stu.setAddress(resultSet.getString("address"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setName(resultSet.getString("name"));
                stu.setBirthday(new java.util.Date(resultSet.getDate("birthday").getTime()));
                // list.add(stu);
                return stu;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }
        return null;
    }

    @Override
    public List<Student> listByName(String name) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        String sql = "select id,name,birthday,address,phone from student where name like ?";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            resultSet = ps.executeQuery();
            List<Student> list = new ArrayList<>();
            while (resultSet.next()) {
                Student stu = new Student();
                stu.setId(resultSet.getInt("id"));
                stu.setAddress(resultSet.getString("address"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setName(resultSet.getString("name"));
                stu.setBirthday(new java.util.Date(resultSet.getDate("birthday").getTime()));
                list.add(stu);

            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }
        return null;
    }

    @Override
    public Student get(String phone) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        String sql = "select id,name,birthday,address,phone from student where phone =?";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);
            ps.setString(1, phone);
            resultSet = ps.executeQuery();
            List<Student> list = new ArrayList<>();
            if (resultSet.next()) {
                Student stu = new Student();
                stu.setId(resultSet.getInt("id"));
                stu.setAddress(resultSet.getString("address"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setName(resultSet.getString("name"));
                stu.setBirthday(new java.util.Date(resultSet.getDate("birthday").getTime()));
                // list.add(stu);
                return stu;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, ps, null);
        }
        return null;
    }
}
